package org.jeecg;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.locationtech.jts.geom.*;
import org.locationtech.jts.io.WKBWriter;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;
import org.opengis.referencing.operation.TransformException;

import java.io.File;
import java.sql.*;
import java.util.Map;

public class Shp2PostGIS_WGS84_Polygon {

    // 坐标系转换器（32650 -> 4326）
    private static MathTransform transform;

    static {
        try {
            CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:32650");
            CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:4326");
            transform = CRS.findMathTransform(sourceCRS, targetCRS, true);
        } catch (FactoryException e) {
            e.printStackTrace();
            throw new RuntimeException("坐标系转换器初始化失败", e);
        }
    }

    public static void main(String[] args) {
        String shpPath = "F:\\地质数据\\地形坡度\\地形坡度底图.shp";

        // PostgreSQL JDBC配置
        String jdbcUrl = "jdbc:postgresql://123.56.202.52:5432/gisdb";
        String username = "postgres";
        String password = "wwq20010215";
        String tableName = "geotype_wgs84_polygons1"; // 使用新表名

        Connection conn = null;
        PreparedStatement pstmt = null;
        SimpleFeatureIterator iterator = null;
        DataStore shpDataStore = null;
        WKBWriter wkbWriter = new WKBWriter(); // WKB写入器

        try {
            long programStart = System.currentTimeMillis();

            // 1. 加载Shapefile
            File shpFile = new File(shpPath);
            shpDataStore = DataStoreFinder.getDataStore(Map.of(
                    "url", shpFile.toURI().toURL(),
                    "create spatial index", Boolean.FALSE
            ));
            if (shpDataStore == null) throw new RuntimeException("无法加载Shapefile数据源");

            String typeName = shpDataStore.getTypeNames()[0];
            SimpleFeatureCollection features = shpDataStore.getFeatureSource(typeName).getFeatures();
            SimpleFeatureType sourceType = shpDataStore.getSchema(typeName);
            System.out.println("Shapefile字段信息:");
            sourceType.getAttributeDescriptors().forEach(attr ->
                    System.out.println("  " + attr.getLocalName() + " : " +
                            attr.getType().getBinding().getSimpleName()));
            long featureCount = features.size();
            System.out.println("找到 " + featureCount + " 条记录");

            // 2. 连接PostgreSQL
            conn = DriverManager.getConnection(jdbcUrl, username, password);
            conn.setAutoCommit(false); // 禁用自动提交

            // 3. 创建表（使用WGS84坐标系，存储Polygon）
            createTable(conn, tableName);
            System.out.println("表 " + tableName + " 创建成功（坐标系: EPSG:4326, 类型: Polygon）");

            // 4. 准备批量插入语句
            String insertSQL = "INSERT INTO " + tableName + " (id, the_geom, gridcode) " +
                    "VALUES (?, ST_GeomFromWKB(?, 4326), ?)"; // 4326坐标系
            pstmt = conn.prepareStatement(insertSQL);

            // 5. 导入数据（转换为WGS84坐标系，并提取为Polygon）
            System.out.println("开始导入数据并转换为Polygon...");
            iterator = features.features();
            final int BATCH_SIZE = 5000;  // 批量大小
            int totalCount = 0; // 总记录数
            int currentBatch = 0; // 当前批次计数
            long batchStartTime = System.currentTimeMillis();
            long lastPrintTime = System.currentTimeMillis();

            while (iterator.hasNext()) {
                SimpleFeature feature = iterator.next();

                // 获取原始ID
                Long id = (Long) feature.getAttribute("Id");
                if (id == null) {
                    System.out.println("警告: 跳过ID为空的记录");
                    continue;
                }

                // 几何对象 - 转换为WGS84坐标系
                MultiPolygon multiPolygon = (MultiPolygon) feature.getAttribute("the_geom");
                System.out.println("multiPolygon = " + multiPolygon);

                Geometry transformedGeom = JTS.transform(multiPolygon, transform); // 坐标系转换
//                System.out.println("transformedGeom = " + transformedGeom);
//                int i =1/0;
                // 提取第一个Polygon
                Polygon polygon = extractFirstPolygon(transformedGeom, id);
                
                // gridcode (Long)
                Long gridcode = (Long) feature.getAttribute("gridcode");
                long gridcodeValue = gridcode != null ? gridcode : -1;

                // 设置参数
                pstmt.setLong(1, id);
                pstmt.setBytes(2, wkbWriter.write(polygon)); // WKB格式
                pstmt.setLong(3, gridcodeValue);

                pstmt.addBatch();
                totalCount++;
                currentBatch++;

                // 定期提交批次
                if (currentBatch >= BATCH_SIZE) {
                    executeBatch(conn, pstmt, currentBatch, batchStartTime);
                    currentBatch = 0;
                    batchStartTime = System.currentTimeMillis();
                }

                // 每5秒打印一次进度
                long currentTime = System.currentTimeMillis();
                if (currentTime - lastPrintTime > 5000) {
                    System.out.printf("总进度: %d/%d 条 (%.1f%%)%n",
                            totalCount, featureCount,
                            (totalCount * 100.0) / featureCount);
                    lastPrintTime = currentTime;
                }
            }

            // 执行最后一批
            if (currentBatch > 0) {
                executeBatch(conn, pstmt, currentBatch, batchStartTime);
            }

            System.out.println("数据导入完成! 总导入记录数: " + totalCount);

            // 6. 创建空间索引
            createSpatialIndex(conn, tableName);
            System.out.println("空间索引创建完成");

            System.out.println("正在验证坐标系和几何类型...");
            // 7. 验证坐标系和几何类型
            verifyGeometry(conn, tableName);

            // 8. 测试查询
            testQuery(conn, tableName, 116.13647460937501, 25.962983554822678);

            // 输出总耗时
            long importDuration = System.currentTimeMillis() - programStart;
            System.out.println("========================================");
            System.out.printf("导入完成! 共导入 %d 条记录, 总耗时 %d ms (%.1f 分钟)%n",
                    totalCount, importDuration, importDuration / 60000.0);
            System.out.printf("平均速率: %.0f 条/秒%n", totalCount * 1000.0 / importDuration);
            System.out.println("========================================");

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("导入失败: " + e.getMessage());
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                System.err.println("回滚失败: " + ex.getMessage());
            }
        } finally {
            // 关闭所有资源
            try {
                if (iterator != null) iterator.close();
                if (shpDataStore != null) shpDataStore.dispose();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 提取MultiPolygon中的第一个Polygon
     * 
     * @param geometry 转换后的几何对象
     * @param id 原始ID（用于错误日志）
     * @return 第一个Polygon
     * @throws RuntimeException 如果无法提取Polygon
     */
    private static Polygon extractFirstPolygon(Geometry geometry, Long id) {
        if (geometry instanceof Polygon) {
            // 如果已经是Polygon，直接返回
            return (Polygon) geometry;
        } else if (geometry instanceof MultiPolygon) {
            // 提取MultiPolygon中的第一个Polygon
            MultiPolygon multiPolygon = (MultiPolygon) geometry;
            if (multiPolygon.getNumGeometries() > 0) {
                Geometry firstGeom = multiPolygon.getGeometryN(0);
                if (firstGeom instanceof Polygon) {
                    return (Polygon) firstGeom;
                } else {
                    System.err.printf("警告: ID %d 的MultiPolygon的第一个几何部件不是Polygon (实际: %s)%n",
                            id, firstGeom.getGeometryType());
                }
            } else {
                System.err.printf("警告: ID %d 的MultiPolygon没有包含任何几何部件%n", id);
            }
        } else {
            System.err.printf("警告: ID %d 的几何对象既不是Polygon也不是MultiPolygon (实际: %s)%n",
                    id, geometry.getGeometryType());
        }
        
        // 如果无法提取有效的Polygon，抛出异常
        throw new RuntimeException("无法从几何对象中提取有效的Polygon");
    }

    // 创建表（使用WGS84坐标系，存储Polygon）
    private static void createTable(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // 删除现有表（如果存在）
            stmt.execute("DROP TABLE IF EXISTS " + tableName + " CASCADE");

            // 创建新表，存储Polygon
            String createSQL = "CREATE TABLE " + tableName + " (" +
                    "id BIGINT PRIMARY KEY, " +      // 原始Shapefile中的ID
                    "the_geom GEOMETRY(Polygon, 4326), " + // 4326坐标系，Polygon类型
                    "gridcode BIGINT)";

            stmt.execute(createSQL);
            conn.commit();
            System.out.println("表创建成功");
        }
    }

    // 执行批处理
    private static void executeBatch(Connection conn, PreparedStatement pstmt, int batchSize, long batchStartTime)
            throws SQLException {
        long start = System.currentTimeMillis();
        pstmt.executeBatch();
        conn.commit();
        long duration = System.currentTimeMillis() - batchStartTime;

        double rate = batchSize * 1000.0 / duration;
        System.out.printf("已提交批次: %d条, 耗时: %d ms, 速率: %.0f 条/秒%n",
                batchSize, duration, rate);
    }

    // 创建空间索引
    private static void createSpatialIndex(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String indexSQL = "CREATE INDEX " + tableName + "_geom_idx " +
                    "ON " + tableName + " USING GIST (the_geom)";
            stmt.execute(indexSQL);
            conn.commit();
            System.out.println("空间索引创建成功");

            // 收集统计信息
            stmt.execute("ANALYZE " + tableName);
            conn.commit();
            System.out.println("统计信息更新完成");
        }
    }

    // 验证坐标系和几何类型
    private static void verifyGeometry(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT ST_SRID(the_geom) AS srid, " +
                     "ST_GeometryType(the_geom) AS geom_type " +
                     "FROM " + tableName + " LIMIT 1")) {

            if (rs.next()) {
                int srid = rs.getInt("srid");
                String geomType = rs.getString("geom_type");
                
                if (srid == 4326) {
                    System.out.println("✅ 坐标系验证成功: SRID=" + srid);
                } else {
                    System.out.println("❌ 坐标系验证失败: SRID=" + srid + " (期望4326)");
                }
                
                if ("ST_Polygon".equalsIgnoreCase(geomType)) {
                    System.out.println("✅ 几何类型验证成功: " + geomType);
                } else {
                    System.out.println("❌ 几何类型验证失败: " + geomType + " (期望ST_Polygon)");
                }
            }
        }
    }

    // 测试查询
    private static void testQuery(Connection conn, String tableName, double lng, double lat)
            throws SQLException {
        String sql = "SELECT id, gridcode, "
                + "ST_AsText(the_geom) AS geom_text "
                + "FROM " + tableName + " "
                + "WHERE ST_Contains("
                + "    the_geom, "
                + "    ST_SetSRID(ST_MakePoint(?, ?), 4326)"
                + ") LIMIT 1";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, lng);
            pstmt.setDouble(2, lat);

            System.out.println("\n测试经纬度查询: (" + lng + ", " + lat + ")");
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    long id = rs.getLong("id");
                    int gridcode = rs.getInt("gridcode");
                    String geomText = rs.getString("geom_text");

                    System.out.println("✅ 查询成功!");
                    System.out.println("  记录ID: " + id);
                    System.out.println("  gridcode: " + gridcode);
                    System.out.println("  几何数据: " + (geomText != null ? geomText.substring(0, Math.min(100, geomText.length())) : "null") + "...");
                } else {
                    System.out.println("❌ 未找到包含该点的多边形");
                    // 查询最近的多边形
                    queryNearest(conn, tableName, lng, lat);
                }
            }
        }
    }

    // 查询最近的多边形
    private static void queryNearest(Connection conn, String tableName, double lng, double lat)
            throws SQLException {
        String sql = "SELECT id, gridcode, "
                + "ST_Distance("
                + "    the_geom, "
                + "    ST_SetSRID(ST_MakePoint(?, ?), 4326)"
                + ") AS distance "
                + "FROM " + tableName + " "
                + "ORDER BY distance "
                + "LIMIT 1";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, lng);
            pstmt.setDouble(2, lat);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    long id = rs.getLong("id");
                    double distance = rs.getDouble("distance");

                    System.out.println("ℹ️ 最近的多边形:");
                    System.out.println("  ID: " + id);
                    System.out.printf("  距离: %.6f 度 ≈ %.2f 米\n",
                            distance, distance * 111000); // 粗略估算
                }
            }
        }
    }
}